package com.imut.lagain.repository;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.imut.lagain.entity.AnswerLockBackup;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

/**
 * 答案锁备份数据访问层
 */
@Mapper
public interface AnswerLockBackupRepository extends BaseMapper<AnswerLockBackup> {

    /**
     * 根据锁ID查找所有备份
     */
    @Select("SELECT * FROM answer_lock_backup WHERE lock_id = #{lockId} AND deleted_at IS NULL ORDER BY created_at DESC")
    List<AnswerLockBackup> findByLockIdAndDeletedAtIsNullOrderByCreatedAtDesc(@Param("lockId") Long lockId);

    /**
     * 根据用户ID查找所有备份
     */
    @Select("SELECT * FROM answer_lock_backup WHERE user_id = #{userId} AND deleted_at IS NULL ORDER BY created_at DESC")
    List<AnswerLockBackup> findByUserIdAndDeletedAtIsNullOrderByCreatedAtDesc(@Param("userId") Long userId);

    /**
     * 根据ID和用户ID查找备份
     */
    @Select("SELECT * FROM answer_lock_backup WHERE id = #{id} AND user_id = #{userId} AND deleted_at IS NULL")
    Optional<AnswerLockBackup> findByIdAndUserId(@Param("id") Long id, @Param("userId") Long userId);

    /**
     * 根据锁ID和用户ID查找备份
     */
    @Select("SELECT * FROM answer_lock_backup WHERE lock_id = #{lockId} AND user_id = #{userId} AND deleted_at IS NULL ORDER BY created_at DESC")
    List<AnswerLockBackup> findByLockIdAndUserIdAndDeletedAtIsNullOrderByCreatedAtDesc(@Param("lockId") Long lockId, @Param("userId") Long userId);

    /**
     * 根据锁ID和备份类型查找备份
     */
    @Select("SELECT * FROM answer_lock_backup WHERE lock_id = #{lockId} AND backup_type = #{backupType} AND deleted_at IS NULL ORDER BY created_at DESC")
    List<AnswerLockBackup> findByLockIdAndBackupTypeAndDeletedAtIsNullOrderByCreatedAtDesc(@Param("lockId") Long lockId, @Param("backupType") String backupType);

    /**
     * 查找最新的备份
     */
    @Select("SELECT * FROM answer_lock_backup WHERE lock_id = #{lockId} AND deleted_at IS NULL ORDER BY created_at DESC LIMIT 1")
    Optional<AnswerLockBackup> findTopByLockIdAndDeletedAtIsNullOrderByCreatedAtDesc(@Param("lockId") Long lockId);

    /**
     * 根据锁ID和备份版本查找备份
     */
    @Select("SELECT * FROM answer_lock_backup WHERE lock_id = #{lockId} AND backup_version = #{backupVersion} AND deleted_at IS NULL")
    Optional<AnswerLockBackup> findByLockIdAndBackupVersionAndDeletedAtIsNull(@Param("lockId") Long lockId, @Param("backupVersion") Integer backupVersion);

    /**
     * 根据备份状态查找备份
     */
    @Select("SELECT * FROM answer_lock_backup WHERE backup_status = #{backupStatus} AND deleted_at IS NULL")
    List<AnswerLockBackup> findByBackupStatusAndDeletedAtIsNull(@Param("backupStatus") String backupStatus);

    /**
     * 根据用户ID和备份状态查找备份
     */
    @Select("SELECT * FROM answer_lock_backup WHERE user_id = #{userId} AND backup_status = #{backupStatus} AND deleted_at IS NULL")
    List<AnswerLockBackup> findByUserIdAndBackupStatusAndDeletedAtIsNull(@Param("userId") Long userId, @Param("backupStatus") String backupStatus);

    /**
     * 查找过期的备份
     */
    @Select("SELECT * FROM answer_lock_backup WHERE expires_at <= #{currentTime} AND deleted_at IS NULL")
    List<AnswerLockBackup> findExpiredBackups(@Param("currentTime") LocalDateTime currentTime);

    /**
     * 查找需要自动清理的备份
     */
    @Select("SELECT * FROM answer_lock_backup WHERE auto_cleanup = true AND expires_at <= #{currentTime} AND deleted_at IS NULL")
    List<AnswerLockBackup> findBackupsForCleanup(@Param("currentTime") LocalDateTime currentTime);

    /**
     * 根据验证状态查找备份
     */
    @Select("SELECT * FROM answer_lock_backup WHERE verification_status = #{verificationStatus} AND deleted_at IS NULL")
    List<AnswerLockBackup> findByVerificationStatusAndDeletedAtIsNull(@Param("verificationStatus") String verificationStatus);

    /**
     * 根据备份链ID查找备份
     */
    @Select("SELECT * FROM answer_lock_backup WHERE backup_chain_id = #{backupChainId} AND deleted_at IS NULL ORDER BY created_at ASC")
    List<AnswerLockBackup> findByBackupChainIdAndDeletedAtIsNullOrderByCreatedAtAsc(@Param("backupChainId") String backupChainId);

    /**
     * 根据父备份ID查找子备份
     */
    @Select("SELECT * FROM answer_lock_backup WHERE parent_backup_id = #{parentBackupId} AND deleted_at IS NULL ORDER BY created_at ASC")
    List<AnswerLockBackup> findByParentBackupIdAndDeletedAtIsNullOrderByCreatedAtAsc(@Param("parentBackupId") Long parentBackupId);

    /**
     * 根据云备份ID查找备份
     */
    @Select("SELECT * FROM answer_lock_backup WHERE cloud_backup_id = #{cloudBackupId} AND deleted_at IS NULL")
    Optional<AnswerLockBackup> findByCloudBackupIdAndDeletedAtIsNull(@Param("cloudBackupId") String cloudBackupId);

    /**
     * 统计锁的备份数量
     */
    @Select("SELECT COUNT(*) FROM answer_lock_backup WHERE lock_id = #{lockId} AND deleted_at IS NULL")
    Long countByLockId(@Param("lockId") Long lockId);

    /**
     * 统计用户的备份数量
     */
    @Select("SELECT COUNT(*) FROM answer_lock_backup WHERE user_id = #{userId} AND deleted_at IS NULL")
    Long countByUserId(@Param("userId") Long userId);

    /**
     * 按备份类型统计用户备份
     */
    @Select("SELECT backup_type, COUNT(*) FROM answer_lock_backup WHERE user_id = #{userId} AND deleted_at IS NULL GROUP BY backup_type")
    List<Object[]> countByUserIdGroupByBackupType(@Param("userId") Long userId);

    /**
     * 按备份状态统计用户备份
     */
    @Select("SELECT backup_status, COUNT(*) FROM answer_lock_backup WHERE user_id = #{userId} AND deleted_at IS NULL GROUP BY backup_status")
    List<Object[]> countByUserIdGroupByBackupStatus(@Param("userId") Long userId);

    /**
     * 计算用户备份总大小
     */
    @Select("SELECT COALESCE(SUM(backup_size), 0) FROM answer_lock_backup WHERE user_id = #{userId} AND deleted_at IS NULL")
    Long getTotalBackupSizeByUserId(@Param("userId") Long userId);

    /**
     * 计算锁备份总大小
     */
    @Select("SELECT COALESCE(SUM(backup_size), 0) FROM answer_lock_backup WHERE lock_id = #{lockId} AND deleted_at IS NULL")
    Long getTotalBackupSizeByLockId(@Param("lockId") Long lockId);

    /**
     * 查找用户最近的10个备份
     */
    @Select("SELECT * FROM answer_lock_backup WHERE user_id = #{userId} AND deleted_at IS NULL ORDER BY created_at DESC LIMIT 10")
    List<AnswerLockBackup> findTop10ByUserIdAndDeletedAtIsNullOrderByCreatedAtDesc(@Param("userId") Long userId);

    /**
     * 查找锁最近的5个备份
     */
    @Select("SELECT * FROM answer_lock_backup WHERE lock_id = #{lockId} AND deleted_at IS NULL ORDER BY created_at DESC LIMIT 5")
    List<AnswerLockBackup> findTop5ByLockIdAndDeletedAtIsNullOrderByCreatedAtDesc(@Param("lockId") Long lockId);

    /**
     * 获取用户最常用的备份类型
     */
    @Select("SELECT backup_type FROM answer_lock_backup WHERE user_id = #{userId} AND deleted_at IS NULL GROUP BY backup_type ORDER BY COUNT(*) DESC")
    List<String> getMostUsedBackupTypesByUserId(@Param("userId") Long userId);

    /**
     * 计算用户备份成功率
     */
    @Select("SELECT (COUNT(CASE WHEN backup_status = 'completed' THEN 1 END) * 100.0 / COUNT(*)) FROM answer_lock_backup WHERE user_id = #{userId} AND deleted_at IS NULL")
    Double getBackupSuccessRateByUserId(@Param("userId") Long userId);

    /**
     * 计算用户平均备份大小
     */
    @Select("SELECT AVG(backup_size) FROM answer_lock_backup WHERE user_id = #{userId} AND backup_status = 'completed' AND deleted_at IS NULL")
    Double getAverageBackupSizeByUserId(@Param("userId") Long userId);

    /**
     * 获取用户备份频率统计
     */
    @Select("SELECT YEAR(created_at), MONTH(created_at), COUNT(*) FROM answer_lock_backup WHERE user_id = #{userId} AND deleted_at IS NULL GROUP BY YEAR(created_at), MONTH(created_at) ORDER BY YEAR(created_at) DESC, MONTH(created_at) DESC")
    List<Object[]> getBackupFrequencyByUserId(@Param("userId") Long userId);

    /**
     * 查找已恢复的备份
     */
    @Select("SELECT * FROM answer_lock_backup WHERE restore_count > 0 AND deleted_at IS NULL ORDER BY last_restored_at DESC")
    List<AnswerLockBackup> findRestoredBackups();

    /**
     * 根据优先级查找备份
     */
    @Select("SELECT * FROM answer_lock_backup WHERE priority >= #{priority} AND deleted_at IS NULL ORDER BY priority DESC, created_at DESC")
    List<AnswerLockBackup> findByPriorityGreaterThanEqualAndDeletedAtIsNullOrderByPriorityDescCreatedAtDesc(@Param("priority") Integer priority);

    /**
     * 查找失败的备份
     */
    @Select("SELECT * FROM answer_lock_backup WHERE backup_status IN ('failed', 'corrupted') AND deleted_at IS NULL ORDER BY created_at DESC")
    List<AnswerLockBackup> findFailedBackups();

    /**
     * 查找需要重新验证的备份
     */
    @Select("SELECT * FROM answer_lock_backup WHERE (verification_status = 'failed' OR (verification_status = 'pending' AND created_at < #{threshold})) AND deleted_at IS NULL")
    List<AnswerLockBackup> findBackupsNeedingReverification(@Param("threshold") LocalDateTime threshold);

    /**
     * 查找大型备份
     */
    @Select("SELECT * FROM answer_lock_backup WHERE backup_size > #{sizeThreshold} AND deleted_at IS NULL ORDER BY backup_size DESC")
    List<AnswerLockBackup> findLargeBackups(@Param("sizeThreshold") Long sizeThreshold);

    /**
     * 获取系统备份统计
     */
    @Select("SELECT COUNT(*), COALESCE(SUM(backup_size), 0), COALESCE(AVG(backup_size), 0), COUNT(CASE WHEN backup_status = 'completed' THEN 1 END) FROM answer_lock_backup WHERE deleted_at IS NULL")
    Object[] getSystemBackupStatistics();

    /**
     * 获取最活跃的备份用户
     */
    @Select("SELECT user_id, COUNT(*) as backup_count FROM answer_lock_backup WHERE created_at >= #{threshold} AND deleted_at IS NULL GROUP BY user_id ORDER BY backup_count DESC")
    List<Object[]> getMostActiveBackupUsers(@Param("threshold") LocalDateTime threshold);

    /**
     * 获取备份位置分布
     */
    @Select("SELECT backup_location, COUNT(*) FROM answer_lock_backup WHERE backup_location IS NOT NULL AND deleted_at IS NULL GROUP BY backup_location ORDER BY COUNT(*) DESC")
    List<Object[]> getBackupLocationDistribution();

    /**
     * 更新过期备份状态
     */
    @Update("UPDATE answer_lock_backup SET backup_status = 'expired' WHERE expires_at <= #{currentTime} AND backup_status != 'expired'")
    int updateExpiredBackups(@Param("currentTime") LocalDateTime currentTime);

    /**
     * 删除过期的自动清理备份
     */
    @Update("UPDATE answer_lock_backup SET deleted_at = #{currentTime} WHERE auto_cleanup = true AND expires_at <= #{currentTime} AND deleted_at IS NULL")
    int deleteExpiredAutoCleanupBackups(@Param("currentTime") LocalDateTime currentTime);
}